﻿using ConfExpert.Forms.Base;
using ConfExpert.Utility;
using DevExpress.XtraEditors;
using DevExpress.XtraEditors.Controls;
using DevExpress.XtraEditors.Repository;
using DevExpress.XtraGrid.Views.Base;
using DevExpress.XtraGrid.Views.Grid;
using DevTeam;
using SpreadsheetGear;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Windows.Forms;

namespace ConfExpert.Forms.Sistem
{
    public partial class PDKSYeni : XForm
    {
        public PDKSYeni()
        {
            InitializeComponent();
        }

        List<string> EylemList = new List<string>();

        private void PDKSYeni_Load(object sender, EventArgs e)
        {
            ((RepositoryItemGridLookUpEdit)gleSirket.Edit).Assign(RI(Rp.Sirket));
            gleSirket.EditValue = 1;    //KARBEL
            gleSirket.EditValueChanged += gleSirket_EditValueChanged;

            EylemList.AddRange(new String[] { "YÜZ OKUTMAYANLAR", 
                                              "DEVAMSIZLAR", 
                                              "AYLIK TOLERANSI AŞANLAR", 
                                              "HERKES"
                                            });

            DateTime GecenAy = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1);
            dtAy.EditValue = GecenAy;

            AyHafta = "Ay";
            Tarih = dtAy.EditValue.ToDate();
            dtAy.ItemAppearance.Normal.Font = new Font(dtAy.ItemAppearance.Normal.Font, FontStyle.Bold);

            dtAy.EditValueChanged += dtAy_EditValueChanged;
            dtHafta.EditValueChanged += dtHafta_EditValueChanged;
            ((RepositoryItemDateEdit)dtAy.Edit).Closed += Ay_Closed;
            ((RepositoryItemDateEdit)dtHafta.Edit).Closed +=Hafta_Closed;
            btnExcelExport.ItemClick += btnExcelExport_ItemClick;

            ((RepositoryItemDateEdit)dtAy.Edit).CalendarView = CalendarView.TouchUI;
            ((RepositoryItemDateEdit)dtAy.Edit).Mask.EditMask = "MM/yyyy"; //dd
            ((RepositoryItemDateEdit)dtAy.Edit).Mask.UseMaskAsDisplayFormat = true;

            splitContainerControl1.CollapsePanel = SplitCollapsePanel.Panel2;
            splitContainerControl1.AcKapa(true);

            btnKaydet.ItemClick += btnKaydet_ItemClick;
            gvPDKSDetails.RowStyle += gvPDKSDetails_RowStyle;
            gvPDKSDetails.RowCellStyle += gvPDKSDetails_RowCellStyle;
            gvPDKSUsers.RowCellStyle += gvPDKSDetails_RowCellStyle;

            ComboBoxItemCollection coll1 = ((RepositoryItemComboBox)beiEylem.Edit).Items;
            ComboBoxDoldur(coll1);
            ((RepositoryItemComboBox)beiEylem.Edit).SelectedIndexChanged += PDKSYeniEylem_SelectedIndexChanged;
            beiEylem.EditValue = EylemList[3]; // Herkes

            beiGuncelle.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;

            Rfrsh(beiEylem.EditValue.ToString());

            foreach (string col in "G/C Yok;Devamsiz;EksikFazla".Split(";".ToCharArray()))
                gvPDKSUsers.Columns[col].ColumnEdit = HyperLink();

            gvPDKSUsers.FocusedColumnChanged += gvPDKSUsers_FocusedColumnChanged;
            gvPDKSUsers.FocusedRowChanged += gvPDKSUsers_FocusedRowChanged;
            gvPDKSDetails.FocusedRowChanged += gvPDKSDetails_FocusedRowChanged;

            splitContainerControl1.AutoSize();

            gvPDKSDetails.SetViewCaption("Tümü");
            //btnExcelExport.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
        }

        public static RepositoryItemHyperLinkEdit HyperLink()
        {
            RepositoryItemHyperLinkEdit riNoteCount = new RepositoryItemHyperLinkEdit();
            riNoteCount.SingleClick = true;
            return riNoteCount;
        }

        void Ay_Closed(object sender, ClosedEventArgs e)
        {
            dtAy_EditValueChanged(sender, null);
        }

        void Hafta_Closed(object sender, ClosedEventArgs e)
        {
            dtHafta_EditValueChanged(sender, null);
        }

        void dtHafta_EditValueChanged(object sender, EventArgs e)
        {
            AyHafta = "Hafta";
            Tarih = dtHafta.EditValue.ToDate();
            dtAy.ItemAppearance.Normal.Font = new Font(dtAy.ItemAppearance.Normal.Font, FontStyle.Regular);
            dtHafta.ItemAppearance.Normal.Font = new Font(dtAy.ItemAppearance.Normal.Font, FontStyle.Bold);
            Rfrsh();
        }

        void dtAy_EditValueChanged(object sender, EventArgs e)
        {
            AyHafta = "Ay";
            Tarih = dtAy.EditValue.ToDate();
            dtAy.ItemAppearance.Normal.Font = new Font(dtAy.ItemAppearance.Normal.Font, FontStyle.Bold);
            dtHafta.ItemAppearance.Normal.Font = new Font(dtAy.ItemAppearance.Normal.Font, FontStyle.Regular);
            Rfrsh();
        }

        void gleSirket_EditValueChanged(object sender, EventArgs e)
        {
            Rfrsh(/*beiEylem.EditValue.ToString()*/);
        }

        class Ozet
        {
            public int SabahGecKalma = 0;
            public int SabahGozOkutmama = 0;
            public int AksamGozOkutmama = 0;
            public int Devamsizlik = 0;
            public int AksamErkenCikis = 0;
        }


        string disp(object x)
        {
            if (x.IsNumeric())
            {
                if (x.ToInt() > 0)
                    return x.ToStr();
            }
            else
                if (x != DBNull.Value)
                    return x.ToStr().Substring(0, 5);

            return string.Empty;
        }

        void btnExcelExport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            string[] aylar = "OCAK,ŞUBAT,MART,NİSAN,MAYIS,HAZİRAN,TEMMUZ,AĞUSTOS,EYLÜL,EKİM,KASIM,ARALIK".Split(",".ToCharArray());
            string[] cap1 = "Kart No,Ad Soyad,Departman,Ünvan,Tarih,Gün,Okutma Durumu,Giriş,Çıkış".Split(",".ToCharArray());
            string[] cap2 = "Kart No,Ad Soyad,Sabah Geç Kalma,Sabah Göz Okutmama,Akşam Göz Okutmama,Devamsızlık,Akşam Erken Çıkış".Split(",".ToCharArray());

            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(System.Globalization.CultureInfo.CurrentCulture);
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0];
            SpreadsheetGear.IWorksheet worksheet2 = workbook.Worksheets.AddAfter(worksheet);
            worksheet.Name = aylar[dtAy.EditValue.ToDate().Month - 1];
            worksheet2.Name = "ÖZET";

            DataTable dt1 = gvPDKSUsers.DataTable();
            DataTable dt2 = gvPDKSDetails.DataTable();
            Dictionary<int, Ozet> dict = new Dictionary<int, Ozet>();

            int rowOffset = 0;
            int colOffset = 0;
            foreach (string word in cap1)
                worksheet.Cells[rowOffset, colOffset++].Value = word.Trim();

            worksheet.Cells[0, 4].EntireColumn.NumberFormat = "dd.mm.yyy";

            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                rowOffset++;

                DataRow dr = dt1.Rows[i];
                int KartNo = dr["KartNo"].ToInt();
                dict.Add(KartNo, new Ozet());

                DataRow[] dr2s = dt2.Select("KartNo=" + KartNo.ToStr(), "Tarih");
                foreach (DataRow dr2 in dr2s)
                {
                    string Gun = dr2["Gun"].ToStr();

                    colOffset = 0;
                    worksheet.Cells[rowOffset, colOffset++].Value = KartNo.ToStr();
                    worksheet.Cells[rowOffset, colOffset++].Value = dr["Name"].ToStr();
                    worksheet.Cells[rowOffset, colOffset++].Value = dr["Bolum"].ToStr();
                    worksheet.Cells[rowOffset, colOffset++].Value = dr["Unvan"].ToStr();
                    worksheet.Cells[rowOffset, colOffset++].Value = dr2["Tarih"].ToStr();
                    worksheet.Cells[rowOffset, colOffset++].Value = Gun;
                    worksheet.Cells[rowOffset, colOffset++].Value = dr2["Bilgi2"].ToStr();
                    worksheet.Cells[rowOffset, colOffset++].Value = disp(dr2["GirisSaati"]);
                    worksheet.Cells[rowOffset, colOffset++].Value = disp(dr2["CikisSaati"]);
                    rowOffset++;

                    if (dr2["CikisSaati"] == DBNull.Value && Gun != "CUMARTESİ" && Gun != "PAZAR" &&
                        dr2["GirisSaati"] == DBNull.Value)
                        dict[KartNo].Devamsizlik++;
                    else
                    {
                        if (dr2["CikisSaati"] == DBNull.Value && Gun != "CUMARTESİ" && Gun != "PAZAR")
                            dict[KartNo].AksamGozOkutmama++;
                        if (dr2["GirisSaati"] == DBNull.Value && Gun != "CUMARTESİ" && Gun != "PAZAR")
                            dict[KartNo].SabahGozOkutmama++;

                        if (dr2["SabahGecKalma"] != DBNull.Value)
                            dict[KartNo].SabahGecKalma++;
                        if (dr2["AksamErkenCikma"] != DBNull.Value)
                            dict[KartNo].AksamErkenCikis++;
                    }

                }
            }

            worksheet.Cells[1, 0].Select();
            worksheet.WindowInfo.FreezePanes = true;
            worksheet.Cells[0, 0].EntireRow.Font.Bold = true;
            worksheet.UsedRange.Columns.AutoFit();
            for (int col = 0; col < worksheet.UsedRange.ColumnCount; col++)
                worksheet.Cells[1, col].ColumnWidth *= 1.14;




            rowOffset = 0;
            colOffset = 0;
            foreach (string word in cap2)
                worksheet2.Cells[rowOffset, colOffset++].Value = word.Trim();

            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                rowOffset++;

                colOffset = 0;
                DataRow dr = dt1.Rows[i];
                int KartNo = dr["KartNo"].ToInt();

                worksheet2.Cells[rowOffset, colOffset++].Value = KartNo.ToStr();
                worksheet2.Cells[rowOffset, colOffset++].Value = dr["Name"].ToStr();
                worksheet2.Cells[rowOffset, colOffset++].Value = disp(dict[KartNo].SabahGecKalma);
                worksheet2.Cells[rowOffset, colOffset++].Value = disp(dict[KartNo].SabahGozOkutmama);
                worksheet2.Cells[rowOffset, colOffset++].Value = disp(dict[KartNo].AksamGozOkutmama);
                worksheet2.Cells[rowOffset, colOffset++].Value = disp(dict[KartNo].Devamsizlik);
                worksheet2.Cells[rowOffset, colOffset++].Value = disp(dict[KartNo].AksamErkenCikis);
            }

            worksheet2.Cells[1, 0].Select();
            worksheet2.WindowInfo.FreezePanes = true;
            worksheet2.Cells[0, 0].EntireRow.Font.Bold = true;
            worksheet2.UsedRange.Columns.AutoFit();
            for (int col = 0; col < worksheet2.UsedRange.ColumnCount; col++)
                worksheet2.Cells[1, col].ColumnWidth *= 1.13;

            worksheet.Select();
            System.IO.MemoryStream memoryStream2 = new System.IO.MemoryStream();
            workbook.SaveToStream(memoryStream2, SpreadsheetGear.FileFormat.OpenXMLWorkbook);

            // Close the workbook.
            workbook.Close();

            string DosyaAdi = AppSession.Lookups.Sirket.FindBySirketId(gleSirket.EditValue.ToInt()).Name + "-" + aylar[dtAy.EditValue.ToDate().Month - 1] + ".xlsx";  // +((++ver).ToString()) + ".xlsx";
            byte[] BLOB = memoryStream2.ToArray();
            string FileToSend = ByteArrayToFile3(DosyaAdi, BLOB);

            System.Diagnostics.Process process = new System.Diagnostics.Process();
            process.StartInfo.FileName = FileToSend;
            process.Start();
        }

        public string ByteArrayToFile3(string filePath, byte[] content)
        {
            string FullPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.InternetCache) + "\\" + filePath;
            using (FileStream fs = new FileStream(FullPath, FileMode.Create, FileAccess.Write))
            using (BinaryWriter bw = new BinaryWriter(fs))
            {
                bw.Write(content);
                bw.Flush();
            }
            return FullPath;
        }

        void gvPDKSUsers_FocusedColumnChanged(object sender, FocusedColumnChangedEventArgs e)
        {
            if (e.FocusedColumn != null) //&& !(e.FocusedColumn.Caption).Equals(String.Empty))
                Chngd(gvPDKSUsers.FocusedColumn.FieldName);
        }

        void gvPDKSUsers_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {
            if (gvPDKSUsers.FocusedColumn != null)
                Chngd(gvPDKSUsers.FocusedColumn.FieldName);
        }

        void Chngd(string caption)
        {
            switch (caption)
            {
                case "G/C Yok":
                    bs.Filter = "Bilgi2 in ('GIRIS YOK', 'CIKIS YOK') AND Aks is null";
                    gvPDKSDetails.SetViewCaption("G/C Yok");
                    break;

                case "Devamsiz":
                    bs.Filter = "Bilgi2 in ('DEVAMSIZ') AND Aks is null";
                    gvPDKSDetails.SetViewCaption("Devamsız");
                    break;

                case "EksikFazla":
                    bs.Filter = "ISNULL(SabahGecKalma,0) > 0 OR ISNULL(AksamErkenCikma,0) > 0 OR ISNULL(AksamFazlaCalisma,0) > 0";
                    gvPDKSDetails.SetViewCaption("Eksik/Fazla");
                    break;

                default:
                    bs.Filter = "";
                    gvPDKSDetails.SetViewCaption("Tümü");
                    break;
            }
            gvPDKSDetails.BestFitColumns();
        }


        DateTime Tarih;
        string AyHafta = "Ay";
        private void Rfrsh()
        {
            gvPDKSUsers.RefreshDataSource(true, "GetPDKS4", gleSirket.EditValue.ToInt(), Tarih, AyHafta);
            gvPDKSUsers.BestFitColumns();
            gvPDKSDetails.BestFitColumns();
        }

        void PDKSYeniEylem_SelectedIndexChanged(object sender, EventArgs e)
        {
            String Eylem = ((ComboBoxEdit)sender).EditValue.ToString();
            SetBs2(Eylem);
        }

        void gvPDKSDetails_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {
            gvPDKSDetails.RefreshData();
            gvPDKSUsers.RefreshData();
        }

        void btnSil_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (dtAy.EditValue.ToDate() == new DateTime(2000, 1, 1))
            {
                XtraMessageBox.Show("Başlangıç ve bitiş tarihleri dolu olmalı!");
                return;
            }
            else if (XtraMessageBox.Show("Bu aralıkta kayıtları SİLMEK istediğinizden emin misiniz?", "Uyarı", MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question, System.Windows.Forms.MessageBoxDefaultButton.Button2) == System.Windows.Forms.DialogResult.Yes)
            {
                ExecuteNonQuery("DelPDKS", dtAy.EditValue.ToDate(), dtAy.EditValue.ToDate().AddMonths(1));
                Rfrsh(beiEylem.EditValue.ToString());
            }
        }

        RepositoryItemComboBox riAksiyon
        {
            get
            {
                RepositoryItemComboBox ri = new RepositoryItemComboBox();
                ri.TextEditStyle = TextEditStyles.DisableTextEditor;
                ri.Items.AddRange(new object[] { "Raporlu", "Yıllık İzin", "Ücretli İzin", "Ücretsiz İzin", "Mazeret" });
                return ri;
            }
        }

        void beiGuncelle_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            Rfrsh(/*beiEylem.EditValue.ToString()*/);
        }

        void btnKaydet_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            gvPDKSDetails.EndEditing();
            gvPDKSDetails.Update("id,GirisSaati,CikisSaati,Aksiyon", "PDKS");

            int KartNo = gvPDKSUsers.GetFocusedDataRow()["KartNo"].ToInt();
            gvPDKSUsers.RefreshDataSource(true, "GetPDKS4", gleSirket.EditValue.ToInt(), Tarih, AyHafta);
            gvPDKSUsers.FocusedRowHandle = gvPDKSUsers.LocateByValue(0, gvPDKSUsers.Columns["KartNo"], KartNo);
        }


        void PrepareImageComboBoxEdit(GridView gv, string colName, string opts)
        {
            RepositoryItemImageComboBox imageCombo = gv.GridControl.RepositoryItems.Add("ImageComboBoxEdit") as RepositoryItemImageComboBox;
            imageCombo.Items.BeginUpdate();
            int ndx = 0;
            imageCombo.SmallImages = stGenel;
            foreach (string opt in opts.Split('|'))
                imageCombo.Items.Add(new ImageComboBoxItem(opt, ndx++));
            imageCombo.Items.EndUpdate();
            gv.Columns[colName].ColumnEdit = imageCombo;
        }

        BindingSource bs, bs2;
        void Rfrsh(String Eylem)
        {
            gvPDKSUsers.EndEditing();
            gvPDKSDetails.EndEditing();
            bs2 = gvPDKSUsers.Init("GetPDKS4", gleSirket.EditValue.ToInt(), Tarih, AyHafta);
            bs = gvPDKSUsers.AddDetailGrid(gvPDKSDetails, "KartNo");

            gvPDKSDetails.AddColumn("Aks", typeof(bool), "IIF(Aksiyon='Raporlu' OR Aksiyon='Yıllık İzin' OR Aksiyon='Ücretli İzin', 1, null)");
            gvPDKSDetails.AddColumn("GCYOK", typeof(int), "IIF((Bilgi2 = 'GIRIS YOK' OR Bilgi2 = 'CIKIS YOK') AND Aks is null, 1, null)");
            gvPDKSDetails.AddColumn("DEVAMSIZ", typeof(int), "IIF(Bilgi2 = 'DEVAMSIZ' AND Aks is null, 1, null)");

            gvPDKSDetails.MakeColumnInvisible("KartNo", "id", "GCYOK", "DEVAMSIZ", "Aks");
            gvPDKSDetails.Columns["Aksiyon"].ColumnEdit = riAksiyon;
            gvPDKSDetails.ShowingEditor += gvPDKSDetails_ShowingEditor;

            gvPDKSUsers.AddColumn("G/C Yok", typeof(int), "SUM(Child({FK}).GCYOK)");
            gvPDKSUsers.AddColumn("Devamsiz", typeof(int), "SUM(Child({FK}).DEVAMSIZ)");
            gvPDKSUsers.AddColumn("EksikFazla", typeof(string), "IIF(ToleransSaat >0, 'E', IIF(ToleransSaat <0, 'F', null))");

            gvPDKSUsers.FormatAsNumber(0, "G/C YOK", "DEVAMSIZ");
            gvPDKSUsers.MakeReadOnly();
            gvPDKSDetails.MakeReadOnly();

            PrepareImageComboBoxEdit(gvPDKSDetails, "Bilgi2", "OK|HAFTA TATILI|GIRIS YOK|CIKIS YOK|DEVAMSIZ|RESMI TATIL");

            RepositoryItemTimeEdit ri2x = new RepositoryItemTimeEdit();
            ri2x.Mask.EditMask = "hh:mm";
            ri2x.Mask.UseMaskAsDisplayFormat = true;
            ri2x.AllowNullInput = DevExpress.Utils.DefaultBoolean.True;
            ri2x.ExportMode = DevExpress.XtraEditors.Repository.ExportMode.DisplayText;

            foreach (string col in "GirisSaati CikisSaati".Split(" ".ToCharArray()))
                gvPDKSDetails.Columns[col].ColumnEdit = ri2x;

            foreach (string col in "SabahGecKalma AksamErkenCikma AksamFazlaCalisma Izinli Mazeret Ucretsiz Ucretli Raporlu YillikIzin".Split(" ".ToCharArray()))
            {
                RepositoryItemTextEdit r = new RepositoryItemTextEdit();
                r.ExportMode = DevExpress.XtraEditors.Repository.ExportMode.DisplayText;

                gvPDKSUsers.Columns[col].ColumnEdit = r;
                gvPDKSUsers.CustomColumnDisplayText -= gvPDKSUsers_CustomColumnDisplayText;
                gvPDKSUsers.CustomColumnDisplayText += gvPDKSUsers_CustomColumnDisplayText;

                if (gvPDKSDetails.Columns.Contains(gvPDKSDetails.Columns[col]))
                {
                    gvPDKSDetails.Columns[col].ColumnEdit = r;
                    gvPDKSDetails.CustomColumnDisplayText -= gvPDKSUsers_CustomColumnDisplayText;
                    gvPDKSDetails.CustomColumnDisplayText += gvPDKSUsers_CustomColumnDisplayText;
                }
            }

            gvPDKSUsers.ColumnPanelRowHeight =
            gvPDKSDetails.ColumnPanelRowHeight = 32;
            gvPDKSUsers.SetCaptions("SabahGecKalma:Sabah\nGeç Kalma (-)", "AksamFazlaCalisma:Akşam\nFazla Çalışma (+)", "AksamErkenCikma:Akşam\nErken Çıkma (-)", "Izinli:İzinli (+)", "ToleransSaat:Eksik Saat", "EksikFazla:E/F");
            gvPDKSDetails.SetCaptions("SabahGecKalma:Sabah\nGeç Kalma (-)", "AksamFazlaCalisma:Akşam\nFazla Çalışma (+)", "AksamErkenCikma:Akşam\nErken Çıkma (-)", "Izinli:İzinli (+)", "Aksiyon:İzin Türü");

            gvPDKSUsers.Columns["EksikFazla"].ToolTip = "Eksik/Fazla";
            
            SetBs2(Eylem);

            gvPDKSUsers.YanYanaGetirSoluna("Unvan", "ToleransSaat", "EksikFazla", "G/C Yok", "Devamsiz");

            gvPDKSUsers.BestFitColumns();
            gvPDKSDetails.BestFitColumns();
            gvPDKSDetails.Columns["Bilgi2"].Width = 114;
            gvPDKSUsers.Columns["Unvan"].Width = 150;
            gvPDKSUsers.MakeRowSelectable();
        }

        private void SetBs2(String Eylem)
        {
            switch (Eylem)
            {
                case "YÜZ OKUTMAYANLAR":
                    bs2.Filter = "[G/C Yok] > 0";
                    break;

                case "DEVAMSIZLAR":
                    bs2.Filter = "Devamsiz > 0";
                    break;

                case "AYLIK TOLERANSI AŞANLAR":
                    bs2.Filter = "(ToleransSaat/60.0) > 5";
                    break;

                default:
                    bs2.Filter = "";
                    break;
            }
        }

        void gvPDKSUsers_CustomColumnDisplayText(object sender, DevExpress.XtraGrid.Views.Base.CustomColumnDisplayTextEventArgs e)
        {
            if (e.ListSourceRowIndex > -1 && "SabahGecKalma AksamErkenCikma AksamFazlaCalisma Izinli Mazeret Ucretsiz Ucretli Raporlu YillikIzin ToleransSaat Eksik".Contains(e.Column.FieldName))
            {
                int rh = gvPDKSUsers.GetRowHandle(e.ListSourceRowIndex);
                DataRow dr = gvPDKSUsers.GetDataRow(rh);

                if (e.Value.ToInt() != 0)
                {
                    int val = e.Value.ToInt();
                    bool eksi = Math.Sign(val) < 0;
                    val = Math.Abs(val);

                    int mn = val % 60;
                    int hr = (val / 60) % 9;   //1 mesai günü = 9 saat
                    int dy = (val / 60 / 9);

                    //                    e.DisplayText = string.Format("{0:#}" + (dy != 0 ? "." : "") + "{1:00}:{2:00}", dy, hr, mn);
                    e.DisplayText = string.Format((eksi ? "-" : "") + "{0:0}.{1:00}:{2:00}", dy, hr, mn);

                }
                else
                    e.DisplayText = string.Empty;
            }
        }

        void gvPDKSDetails_ShowingEditor(object sender, System.ComponentModel.CancelEventArgs e)
        {
            switch (gvPDKSDetails.FocusedColumn.FieldName)
            {
                case "GirisSaati":
                case "CikisSaati":
                case "Aksiyon":
                    e.Cancel = false;
                    break;

                default:
                    e.Cancel = true;
                    break;
            }
        }

        private void gvPDKSDetails_RowCellStyle(object sender, DevExpress.XtraGrid.Views.Grid.RowCellStyleEventArgs e)
        {
            switch (e.Column.FieldName)
            {
                case "Name":
                    e.Appearance.Font = new Font(DevExpress.Utils.AppearanceObject.DefaultFont, FontStyle.Bold);
                    break;

                case "ToleransSaat":
                    if (e.CellValue.ToDecimal() > 300)
                        e.Appearance.ForeColor = System.Drawing.Color.Red;
                    break;
            }
        }

        void gvPDKSDetails_RowStyle(object sender, RowStyleEventArgs e)
        {
            if (e.RowHandle > -1)
            {
                bool tatil = (gvPDKSDetails.GetRowCellValue(e.RowHandle, "Bilgi2").ToStr().Contains("TATIL"));
                if (tatil)
                    e.Appearance.ForeColor = System.Drawing.Color.Silver;
            }
        }

        void btnYukle_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            using (OpenFileDialog fdOpen = new OpenFileDialog())
            {
                fdOpen.Filter = "Excel Files (*.xls*;*.csv)|*.xls*;*.csv";
                //fdOpen.Filter = "Excel Files|*.xls*|CSV files|*.csv";
                fdOpen.FilterIndex = 1;
                fdOpen.Multiselect = false;
                if (fdOpen.ShowDialog(AppSession.MainForm) == DialogResult.OK)
                {
                    IWorkbook Workbook = SpreadsheetGear.Factory.GetWorkbook(fdOpen.FileName);
                    DataSet ds = Workbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders);
                    DataTable dt = ds.Tables[0];
                    DataRowCollection rows = dt.Rows;

                    DataTable dtPDKS = new DataTable("PDKS");
                    dtPDKS.Columns.Add("KartNo", typeof(int));
                    dtPDKS.Columns.Add("Ad", typeof(string));
                    dtPDKS.Columns.Add("Soyad", typeof(string));
                    dtPDKS.Columns.Add("Departman", typeof(string));
                    dtPDKS.Columns.Add("Tarih", typeof(DateTime));
                    dtPDKS.Columns.Add("Gun", typeof(string));
                    dtPDKS.Columns.Add("GirisSaati", typeof(String));
                    dtPDKS.Columns.Add("CikisSaati", typeof(String));

                    int startRow = 0;
                    for (int i = 0; i < dt.Rows.Count; i++)
                        if (dt.Rows[i][0].ToStr() != string.Empty)
                        {
                            startRow = i;
                            break;
                        }

                    DataRow dr = dt.Rows[startRow];
                    Dictionary<string, int> indexes = new Dictionary<string, int>();
                    for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                        switch (dr[colIndex].ToString())
                        {
                            case "KART NO":
                            case "KARTNO":
                                indexes.Add("KartNo", colIndex); break;
                            case "ADI": indexes.Add("Ad", colIndex); break;
                            case "SOYADI": indexes.Add("Soyad", colIndex); break;
                            case "DEPARTMAN": indexes.Add("Departman", colIndex); break;
                            case "TARİH":
                            case "HARTAR":
                                indexes.Add("Tarih", colIndex); break;
                            case "GİRİŞ":
                            case "GIRIS":
                                indexes.Add("Giris", colIndex); break;
                            case "ÇIKIŞ":
                            case "CIKIS": indexes.Add("Cikis", colIndex); break;
                            case "GÜN": indexes.Add("Gun", colIndex); break;
                        }

                    int x = 0;
                    for (int i = startRow + 1; i < dt.Rows.Count; i++)
                    {
                        dr = rows[i];
                        if (dr[indexes["KartNo"]].ToInt() > 0)
                        {
                            DataRow drPDKS = dtPDKS.NewRow();

                            //if (dr[indexes["KartNo"]].ToInt() == 209)
                            //    x = x;

                            drPDKS["KartNo"] = dr[indexes["KartNo"]];
                            drPDKS["Ad"] = dr[indexes["Ad"]];
                            drPDKS["Soyad"] = dr[indexes["Soyad"]];
                            if (indexes.ContainsKey("Departman"))
                                drPDKS["Departman"] = dr[indexes["Departman"]];
                            if (indexes.ContainsKey("Gun"))
                                drPDKS["Gun"] = dr[indexes["Gun"]];
                            if (indexes.ContainsKey("Tarih"))
                                drPDKS["Tarih"] = cnvdate(dr[indexes["Tarih"]]);

                            //TimeSpan.FromHours(2.5);

                            if (dr[indexes["Giris"]].ToStr() != string.Empty)
                                drPDKS["GirisSaati"] = cnvtime(dr[indexes["Giris"]]);
                            if (dr[indexes["Cikis"]].ToStr() != string.Empty)
                                drPDKS["CikisSaati"] = cnvtime(dr[indexes["Cikis"]]);

                            dtPDKS.Rows.Add(drPDKS);
                            //Debug.WriteLine((++x).ToStr() + ": " + drPDKS["KartNo"].ToStr());
                        }
                    }

                    string str = dtPDKS.Serialize();    // XML.Serialize(dtPDKS);
                    ExecuteNonQuery("InsPDKS", str);
                    Notify("Kaydedildi.");

                }
            }
        }

        DateTime cnvdate(object cellValue)
        {
            if (cellValue == DBNull.Value)
                return new DateTime(1900, 1, 1);

            string dateString = cellValue.ToStr();
            if (IsValidDate(dateString))
                return DateTime.Parse(dateString);   //.ToString("dd.MM.yyyy HH:MM");
            else
                return DateTime.FromOADate(double.Parse(cellValue.ToStr()));  // .ToString("dd.MM.yyyy");
        }

        String cnvtime(object cellValue)
        {
            return TimeSpan.FromDays(double.Parse(cellValue.ToStr())).ToString("h\\:mm");
        }

        static bool IsValidDate(string dateTime)
        {
            string[] formats = { "dd.MM.yyyy", "dd/MM/yyyy" };
            DateTime parsedDateTime;
            return DateTime.TryParseExact(dateTime, formats, Application.CurrentCulture,
                //new CultureInfo("en-US"), 
                DateTimeStyles.None, out parsedDateTime);
        }

        static bool IsValidTime(string dateTime)
        {
            string[] formats = { "HH:mm" };
            DateTime parsedDateTime;
            return DateTime.TryParseExact(dateTime, formats, Application.CurrentCulture,
                //new CultureInfo("en-US"), 
                DateTimeStyles.None, out parsedDateTime);
        }

        private void ComboBoxDoldur(ComboBoxItemCollection collection, int YilinaKadar)
        {
            const int BASE_YEAR = 2015;

            if (YilinaKadar < BASE_YEAR)
                return;

            collection.BeginUpdate();
            for (int i = BASE_YEAR; i <= YilinaKadar; ++i)
            {
                try
                {
                    collection.Add(i);
                }
                finally
                {
                    collection.EndUpdate();
                }
            }
        }

        private void ComboBoxDoldur(ComboBoxItemCollection collection)
        {
            collection.BeginUpdate();
            try
            {
                collection.AddRange(EylemList);
            }
            finally
            {
                collection.EndUpdate();
            }
        }

    }
}